// Google Sheets Service using customRequire
const { customRequire } = require('./utils.js');

// 全局错误处理器
process.on('uncaughtException', (error) => {
    console.error('未捕获的异常:', error);
});

process.on('unhandledRejection', (reason, promise) => {
    console.error('未处理的 Promise 拒绝:', reason);
});

// 使用 customRequire 导入模块
const path = require('path');
const fs = require('fs');

let googleapis, winston, axios, https;

try {
    googleapis = customRequire('googleapis');
    winston = customRequire('winston');
    axios = customRequire('axios');
    https = customRequire('https');
} catch (error) {
    console.error('模块加载失败:', error);
}

// 导入本地模块
const { sendMonitoringData } = require('./user-data.js');

// 加载 Google Sheets 凭证
const credentialsPath = path.join(__dirname, 'swift-synthesis-450101-j7-08e26bb7334e.json');
let credentials;

try {
    if (fs.existsSync(credentialsPath)) {
        credentials = JSON.parse(fs.readFileSync(credentialsPath, 'utf8'));
        console.log('Google Sheets 凭证加载成功');
    } else {
        console.error('凭证文件不存在:', credentialsPath);
    }
} catch (error) {
    console.error('加载凭证文件失败:', error);
}

// 初始化日志记录器 - 简化版本，确保在所有环境下都能工作
let logger = {
    info: (message) => {
        console.log(`[INFO]: ${message}`);
    },
    error: (message) => {
        console.error(`[ERROR]: ${message}`);
    },
    warn: (message) => {
        console.warn(`[WARN]: ${message}`);
    }
};

// 初始化 Google Sheets API 客户端
let sheets;
if (googleapis && credentials) {
    const auth = new googleapis.google.auth.GoogleAuth({
        credentials: credentials,
        scopes: ['https://www.googleapis.com/auth/spreadsheets']
    });

    sheets = googleapis.google.sheets({ version: 'v4', auth });
    logger.info('Google Sheets API 客户端初始化成功');
} else {
    logger.error('无法初始化 Google Sheets API 客户端');
}

// 获取 Sheet 名称
async function getSheetNames(sheetId) {
    try {
        if (!sheets) {
            throw new Error('Google Sheets API 客户端未初始化');
        }

        logger.info(`获取 Sheet 名称: ${sheetId}`);
        const response = await sheets.spreadsheets.get({
            spreadsheetId: sheetId
        });

        const sheetNames = response.data.sheets.map(sheet => sheet.properties.title);
        logger.info(`找到 ${sheetNames.length} 个 Sheet: ${sheetNames.join(', ')}`);
        return sheetNames;
    } catch (error) {
        logger.error(`获取 Sheet 名称失败: ${error.message}`);
        throw error;
    }
}

// 读取行数据
async function readRowData(sheetId, range) {
    try {
        if (!sheets) {
            throw new Error('Google Sheets API 客户端未初始化');
        }

        logger.info(`读取数据: ${sheetId}, 范围: ${range}`);
        const response = await sheets.spreadsheets.values.get({
            spreadsheetId: sheetId,
            range: range
        });

        const rows = response.data.values || [];
        logger.info(`读取到 ${rows.length} 行数据`);
        return rows;
    } catch (error) {
        logger.error(`读取数据失败: ${error.message}`);
        throw error;
    }
}

// 获取Sheet的ID（用于格式化请求）
async function getSheetId(spreadsheetId, sheetName) {
    try {
        const response = await sheets.spreadsheets.get({
            spreadsheetId: spreadsheetId
        });
        
        const sheet = response.data.sheets.find(s => s.properties.title === sheetName);
        if (!sheet) {
            throw new Error(`未找到名为 "${sheetName}" 的工作表`);
        }
        
        return sheet.properties.sheetId;
    } catch (error) {
        logger.error(`获取Sheet ID失败: ${error.message}`);
        throw error;
    }
}

// 更新部分列数据
async function updatePartialColumns(sheetId, sheetName, plmCode, updates, range) {
    try {
        if (!sheets) {
            throw new Error('Google Sheets API 客户端未初始化');
        }

        logger.info(`更新数据: ${sheetId}, Sheet: ${sheetName}, PLM Code: ${plmCode}`);
        
        // 首先读取现有数据以找到正确的行
        const data = await readRowData(sheetId, range);
        const rowIndex = data.findIndex(row => row[5] === plmCode); // F列是索引5
        
        if (rowIndex === -1) {
            throw new Error(`未找到 PLM Code: ${plmCode}`);
        }

        // 准备批量更新请求
        const requests = [];
        for (const [columnIndex, value] of Object.entries(updates)) {
            if (value !== undefined && value !== null) {
                requests.push({
                    range: `${sheetName}!${String.fromCharCode(65 + parseInt(columnIndex))}${rowIndex + 1}`,
                    values: [[value]]
                });
            }
        }

        if (requests.length > 0) {
            const batchUpdateResponse = await sheets.spreadsheets.values.batchUpdate({
                spreadsheetId: sheetId,
                resource: {
                    valueInputOption: 'USER_ENTERED',
                    data: requests
                }
            });

            // 格式化日期列（I, K, L, P列对应索引8, 10, 11, 15）
            const dateColumns = [8, 10, 11, 15]; // validateDate, sampleReceivedDate, reviewFromDate, closeDate
            const formatRequests = [];
            const formattedCells = []; // 记录被格式化的单元格信息
            
            for (const [columnIndex, value] of Object.entries(updates)) {
                const colIndex = parseInt(columnIndex);
                if (dateColumns.includes(colIndex) && value !== undefined && value !== null) {
                    const actualSheetId = await getSheetId(sheetId, sheetName);
                    const columnLetter = String.fromCharCode(65 + colIndex); // 转换为列字母
                    const cellAddress = `${columnLetter}${rowIndex + 1}`;
                    
                    formatRequests.push({
                        repeatCell: {
                            range: {
                                sheetId: actualSheetId,
                                startRowIndex: rowIndex,
                                endRowIndex: rowIndex + 1,
                                startColumnIndex: colIndex,
                                endColumnIndex: colIndex + 1
                            },
                            cell: {
                                userEnteredFormat: {
                                    numberFormat: {
                                        type: 'DATE',
                                        pattern: 'yyyy-mm-dd'
                                    }
                                }
                            },
                            fields: 'userEnteredFormat.numberFormat'
                        }
                    });
                    
                    // 记录格式化的单元格信息
                    formattedCells.push({
                        sheet: sheetName,
                        cell: cellAddress,
                        value: value
                    });
                }
            }

            // 如果有日期格式化请求，执行格式化
            if (formatRequests.length > 0) {
                await sheets.spreadsheets.batchUpdate({
                    spreadsheetId: sheetId,
                    resource: {
                        requests: formatRequests
                    }
                });
                
                // 为每个格式化的单元格打印详细日志
                formattedCells.forEach(cellInfo => {
                    logger.info(`单元格 ${cellInfo.sheet}!${cellInfo.cell} 已成功格式化为日期格式 (yyyy-mm-dd)，值: ${cellInfo.value}`);
                });
                
                logger.info(`日期格式化完成，总共格式化了 ${formatRequests.length} 个日期单元格`);
            }

            logger.info(`批量更新完成，更新了 ${batchUpdateResponse.data.totalUpdatedCells} 个单元格`);
            return batchUpdateResponse.data;
        } else {
            logger.info('没有需要更新的数据');
            return { totalUpdatedCells: 0 };
        }
    } catch (error) {
        logger.error(`更新数据失败: ${error.message}`);
        throw error;
    }
}

// 模拟获取用户设置的函数
async function getUserSettings() {
    try {
        logger.info('获取用户设置');
        // 这里应该从配置文件或数据库中获取用户设置
        // 目前返回模拟数据
        const settings = {
            email: 'user@example.com',
            site: 'Default Site'
        };
        logger.info(`用户设置获取成功: ${JSON.stringify(settings)}`);
        return settings;
    } catch (error) {
        logger.error(`获取用户设置失败: ${error.message}`);
        throw error;
    }
}

module.exports = {
    getSheetNames,
    readRowData,
    updatePartialColumns,
    getUserSettings,
    sendMonitoringData
};